package code.google.jcustomize;

import java.io.*;
import java.sql.*;
import java.util.*;
import org.w3c.dom.*;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;

public class SQLExec {
    private Reader sql = null;
    private Connection conn = null;
    private Statement statement = null;

    private boolean keepformat = false;
    private boolean escapeProcessing = true;
    private boolean continueOnError = false;
    private boolean printResultSet = true;
    private String delimiter = ";";
    private Map<String, String> subst = new HashMap<String, String>();

    private static PrintStream out = System.out;

    public static void main(String[] args) {
        Map<String, String> opts = new HashMap<String, String>();
        for (int i = 0; i < args.length; i++) {
            String arg = args[i];
            if (arg.startsWith("--")) opts.put(arg, args[++i]);
        }

        DataSource ds = null;

        if (opts.containsKey("--config")) {
            InputStream is = null;
            try {
                is = SQLExec.class.getResourceAsStream(opts.get("--config"));
                Document config = parse(is);
                if (config != null) ds = getDataSource(config);
            } catch (Exception e) {
            } finally {
                if (is != null) try { is.close(); } catch (Throwable t) {}
            }
        } else 
            ds = getDataSource(opts);

        if (ds == null) System.exit(1);

        InputStream is  = null;
        Connection conn = null;
        process:
        try {
            if (opts.containsKey("--script"))
                is = SQLExec.class.getResourceAsStream(opts.get("--script"));
            if (is == null) System.exit(2);
            String encoding = opts.containsKey("--encoding") ? opts.get("--encoding") : "utf8";
            Reader r = new BufferedReader(new InputStreamReader(is, encoding));

            conn = ds.getConnection();

            SQLExec engine = new SQLExec(r, conn);
            engine.subst.put("___USER___", conn.getMetaData().getUserName());
            engine.execute();
        } catch (Exception e) {
            out.println(e.getMessage() != null ? e.getMessage() : e.getClass().getName());
        } finally {
            if (conn != null) try { conn.close(); } catch (Throwable t) {}
            if (is   != null) try {   is.close(); } catch (Throwable t) {}
        }
    }

    public SQLExec(Reader sql, Connection conn) {
        this.sql = sql;
        this.conn = conn;
    }

    private void execute() throws SQLException, IOException {
        try {
            statement = conn.createStatement();
            statement.setEscapeProcessing(escapeProcessing);

            StringBuffer sql = new StringBuffer();
            String line;

            BufferedReader in = new BufferedReader(this.sql);

            while ((line = in.readLine()) != null) {
                for (String s : subst.keySet()) line = line.replaceAll(s, subst.get(s));
                if (!keepformat) {
                    line = line.trim();
                }
                if (!keepformat) {
                    if (line.startsWith("//"))  {
                        continue;
                    }
                    if (line.startsWith("--")) {
                        continue;
                    }
                    StringTokenizer st = new StringTokenizer(line);
                    if (st.hasMoreTokens()) {
                        String token = st.nextToken();
                        if ("REM".equalsIgnoreCase(token)) {
                            continue;
                        }
                    }
                }

                if (!keepformat) {
                    sql.append(" ");
                    sql.append(line);
                } else {
                    sql.append("\n");
                    sql.append(line);
                }

                // SQL defines "--" as a comment to EOL
                // and in Oracle it may contain a hint
                // so we cannot just remove it, instead we must end it
                if (!keepformat) {
                    if (line.indexOf("--") >= 0) {
                        sql.append("\n");
                    }
                }

                if (sql.toString().endsWith(delimiter)) {
                    execSQL(sql.substring(0, sql.length() - delimiter.length()));
                    sql.replace(0, sql.length(), "");
                }
            }

            if (sql.length() > 0) {
                execSQL(sql.toString());
            }
        }
        finally {
            if (statement != null) {
                statement.close();
            }
        }

    }

    protected void execSQL(String sql) throws SQLException {
        if ("".equals(sql.trim())) {
            return;
        }

        ResultSet resultSet = null;
        try {
            //out.println("SQL: " + sql);

            boolean ret;
            int updateCount = 0, updateCountTotal = 0;

            ret = statement.execute(sql);
            updateCount = statement.getUpdateCount();
            resultSet = statement.getResultSet();
            do {
                if (!ret) {
                    if (updateCount != -1) {
                        updateCountTotal += updateCount;
                    }
                }
                else {
                    if (printResultSet) {
                        printResults(resultSet, out);
                    }
                }
                ret = statement.getMoreResults();
                if (ret) {
                    updateCount = statement.getUpdateCount();
                    resultSet = statement.getResultSet();
                }
            }
            while (ret);

            //out.println(updateCountTotal + " rows affected");

            SQLWarning warning = conn.getWarnings();
            while (warning != null) {
                out.println(warning + " sql warning");
                warning = warning.getNextWarning();
            }
            conn.clearWarnings();
        }
        catch (SQLException e) {
            out.println("Failed to execute: " + sql);
            e.printStackTrace(out);
            if (continueOnError)
                throw e;
        }
        finally {
            if (resultSet != null) {
                resultSet.close();
            }
        }
    }

    protected void printResults(PrintStream out) throws SQLException {
        ResultSet rs = statement.getResultSet();
        try {
            printResults(rs, out);
        } finally {
            if (rs != null) {
                rs.close();
            }
        }
    }

    protected void printResults(ResultSet rs, PrintStream out) throws SQLException {
        if (rs != null) {
            out.println("Processing new result set.");
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            StringBuffer line = new StringBuffer();
            for (int col = 1; col < columnCount; col++) {
                 line.append(md.getColumnName(col));
                 line.append(",");
            }
            line.append(md.getColumnName(columnCount));
            out.println(line);
            line = new StringBuffer();
            while (rs.next()) {
                boolean first = true;
                for (int col = 1; col <= columnCount; col++) {
                    String columnValue = rs.getString(col);
                    if (columnValue != null) {
                        columnValue = columnValue.trim();
                    }

                    if (first) {
                        first = false;
                    } else {
                        line.append(",");
                    }
                    line.append(columnValue);
                }
                out.println(line);
                line = new StringBuffer();
            }
        }
        out.println();
    }

    public static DataSource getDataSource(Map<String, String> opts) {
        try {
            String url = opts.get("--url");
            if (url == null) return null;
            if (!opts.containsKey("--driver")) return null;
            BasicDataSource ds = new BasicDataSource();
            ds.setDriverClassName(opts.get("--driver"));
            ds.setUrl(url);
            ds.setMaxActive(10);
            ds.setMaxWait(100);
            ds.setDefaultAutoCommit(true);
            ds.setDefaultReadOnly(false);
            ds.setDefaultTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            ds.setDefaultCatalog(null);
            String user = opts.get("--user");
            if (user != null && user.length() > 0) {
                ds.setUsername(user);
                String pwd = opts.get("--password");
                if (pwd != null && pwd.length() > 0) ds.setPassword(pwd);
            }
            if (url.startsWith("jdbc:postgresql:")) {
                ds.setValidationQuery("SELECT 1");
            } else
            if (url.startsWith("jdbc:oracle:")) {
                ds.setValidationQuery("SELECT DUMMY FROM DUAL");
            }

            return ds;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static DataSource getDataSource(Document config) {
        try {
            Map<String, String> opts = new HashMap<String, String>();
            Element root = config.getDocumentElement();
            NodeList nl = root.getElementsByTagName("*");
            for (int i = 0; i < nl.getLength(); i++) {
                Node n = nl.item(i);
                String name = n.getNodeName();
                if ("connection-url".equals(name)) opts.put("--url", n.getTextContent()); else
                if ("driver-class".equals(name)) opts.put("--driver", n.getTextContent()); else
                if ("user-name".equals(name)) opts.put("--user", n.getTextContent()); else
                if ("password".equals(name)) opts.put("--password", n.getTextContent());
            }
            return getDataSource(opts);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    
    private static DocumentBuilderFactory dbf          = null;
    private static DocumentBuilder db                  = null;
    private static Document parse(InputStream is) {
        try {
            if (dbf == null) {
                dbf = DocumentBuilderFactory.newInstance();
                dbf.setNamespaceAware(true);
            }
            if (db == null)
                db = dbf.newDocumentBuilder();
            return db.parse(is);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

}
